<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection" %>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>

<%
       String data = "";
       String sql = "";
       String json = "";
       int errorcode = 0;
       ResultSet res = null;
       model.ConnectionDB connection = new model.ConnectionDB();

        if(request.getParameter("action").equals("seek")){
            sql = "SELECT * FROM seekallcities();";
            //out.print(sql);
            try {
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()) {
            //if(res.next()){
            //HttpSession sessionUser=request.getSession();//Create new Session
            //sessionUser.setAttribute("result",res);
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'nameCity' :'" + res.getString("city_name") + "', ";
                    //data += "'state' :'" + res.getString("city_state") + "', ";
                    //data += "'idCity': " + res.getInt("city_id") + ", ";
                    data += '}';
                }
            }catch(Exception e) {
                out.print(e.getMessage());
                out.print("{'errorcode' : -1}"); //
                connection.getCon().close();
            }
        }else if(request.getParameter("action").equals("changed")){
            sql = "SELECT * FROM getcitydestination('"+request.getParameter("ID")+"')";
            //out.print(sql);
            try {
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()) {
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'nameCityD' :'" + res.getString("nameCity") + "', ";
                    data += '}';
                }
            }catch (Exception e){
                //out.print(res);
                out.print(e.getMessage());
                out.print("{'errorcode' : -1}"); //
                connection.getCon().close();
            }
        }else if(request.getParameter("action").equals("seehours")){
            sql = "SELECT * FROM gethours('"+request.getParameter("txtsource")+"','"+request.getParameter("txtdestination")+"')";
            try {
                //out.print(sql);
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()){
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'idRoute' :'" + res.getInt("id") + "', ";
                    data += "'exitRoute' :'" + res.getString("hourexit") + "', ";
                    data += "'costRoute': " + res.getInt("val") + ", ";
                    data += '}';
                }
             }catch(Exception e){
                out.print(e.getMessage());
                errorcode = -1;
             }
        }else if(request.getParameter("action").equals("addticket")){
            sql = "SELECT * FROM addticketsold('"+request.getParameter("varPlace")+"','"+request.getParameter("varDate")+"','"+request.getParameter("varRoute")+"','"+request.getParameter("varUser")+"')";
            try {
              //  out.print(sql);
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                 while (res.next()){
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'result' :'" + res.getInt(1) + "', ";
                   // data += "'ticketnum' :'" + res.getInt(2) + "', ";
                    data += '}';
                }
             }catch(Exception e){
                out.print(e.getMessage());
                errorcode = -1;
             }
        }else if(request.getParameter("action").equals("deleteticket")){
            sql = "SELECT * FROM deleteticket('"+request.getParameter("ID")+"')";
            try {
                //out.print(sql);
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

            }catch(Exception e){
                out.print(e.getMessage());
                errorcode = -1;
             }
        }else if(request.getParameter("action").equals("seeTicketData")){
            sql = "SELECT * FROM getinfoticket('"+request.getParameter("ID")+"')";
            try {
                //out.print(sql);
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()){
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'ticketuser' :'" + res.getString("username") + "', ";
                    data += "'ticketsource' :'" + res.getString("citysource") + "', ";
                    data += "'ticketdestination' :'" + res.getString("citydestination") + "', ";
                    data += "'tickethour' :'" + res.getString("hourexit") + "', ";
                    data += "'ticketplace': " + res.getInt("id") + ", ";
                    data += "'ticketdate': '" + res.getString("tdate") + "', ";
                    data += "'tickettype': '" + res.getString("ttype") + "'";
                    data += '}';
                }
             }catch(Exception e){
                out.print(e.getMessage());
                errorcode = -1;
             }
        }else if(request.getParameter("action").equals("updateticket")){
            sql = "SELECT * FROM updateticket('"+request.getParameter("ticketplace")+"','"+request.getParameter("date")+"','"+request.getParameter("ticketID")+"')";
            try {
                //out.print(sql);
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()){
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'result' :" + res.getInt(1) + " ";
                    data += '}';
                }
             }catch(Exception e){
                out.print(e.getMessage());
                errorcode = -1;
             }
        }else if(request.getParameter("action").equals("loadbusyplace")){
            sql = "SELECT * FROM getticketsroute("+request.getParameter("ID")+");";
            //out.print(sql);
            try {
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()) {
            //if(res.next()){
            //HttpSession sessionUser=request.getSession();//Create new Session
            //sessionUser.setAttribute("result",res);
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'busyplace' :" + res.getInt(3)+", ";
                    data += "'state' :'" + res.getString(2) + "' ";
                    //data += "'idCity': " + res.getInt("city_id") + ", ";
                    data += '}';
                }
            }catch(Exception e) {
                out.print(e.getMessage());
                out.print("{'errorcode' : -1}"); //
                connection.getCon().close();
            }
        }else if(request.getParameter("action").equals("reserveticket")){
            sql = "SELECT * FROM addticketreserved2('"+request.getParameter("varPlace")+"','"+request.getParameter("varDate")+"','"+request.getParameter("varRoute")+"','"+request.getParameter("varUser")+"')";
            //out.print(sql);
            try {
                connection.connect();
                res = connection.consult(sql);
                connection.getCon().close();

                while (res.next()) {
            //if(res.next()){
            //HttpSession sessionUser=request.getSession();//Create new Session
            //sessionUser.setAttribute("result",res);
                    if (!data.equals("")) {
                        data += ",";
                    }
                    data += '{';
                    data += "'ticketID' :" + res.getInt(1)+" ";
                    //data += "'state' :'" + res.getString(2) + "' ";
                    //data += "'idCity': " + res.getInt("city_id") + ", ";
                    data += '}';
                }
            }catch(Exception e) {
                out.print(e.getMessage());
                out.print("{'errorcode' : -1}"); //
                connection.getCon().close();
            }
        }else
           errorcode = -2; //Incorrect Access

       json = "{'errorcode' : " + errorcode + ", 'data' : [" + data + "]}";
       out.print(json);
%>